package com.hnkywl.sts.dao.tjbb;

import com.hnkywl.sts.dao.organ.GsjgDao;
import com.hnkywl.sts.dao.organ.QyjgDao;
import com.hnkywl.sts.entity.organ.Qyjg;
import com.hnkywl.sts.service.sys.YgService;
import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.transform.AliasToEntityMapResultTransformer;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created with IntelliJ IDEA.
 * User: SDE09
 * Date: 13-9-28
 * Time: 下午5:14
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class YjbbDao extends SimpleHibernateTemplate {

    private GsjgDao gsjgDao;
    private QyjgDao qyjgDao;

    @Autowired
    public void setGsjgDao(GsjgDao gsjgDao) {
        this.gsjgDao = gsjgDao;
    }

    @Autowired
    public void setQyjgDao(QyjgDao qyjgDao) {
        this.qyjgDao = qyjgDao;
    }

    public Page<Map> findKhkctjList(String kh, Long cggsid, String code, String name, String ggxh, String sort, String order, int pageNo, int pageSize) {
        Page<Map> page = new Page<Map>();
        Map<String, Object> param = new HashMap<String, Object>();
        List<String> condition = new ArrayList<String>();
        String countsql = "select count(1)  from (select k.cd_sjzdz_id as cdId,k.jtwz_id,k.cd_sjzdz_id,k.ck_id,sum(k.sl) as jl ," +
                " decode(sum(k.sl * (shj.shj - shj.lxjt)),null,0,sum(k.sl * (shj.shj - shj.lxjt))) as hz," +
                " (select gg.cz||' '||gg.ggxh from t_sys_gg gg where gg.id = k.jtwz_id) as czggxh, " +
                " (select gs.jc from t_sys_gsjg gs where gs.id = k.sbgs_id) as kh,k.sbgs_id, " +
                " (select gss.jc from t_sys_gsjg gss where gss.id = k.cggs_id) as yq, k.cggs_id as cggsId" +
                " from t_biz_kh k  left join t_biz_shj shj on k.id = shj.kh_id" +
                " where k.qk = 0 group by k.cggs_id,k.sbgs_id,k.cd_sjzdz_id,k.ck_id,k.jtwz_id) b,t_sys_gg g where b.jtwz_id = g.id";

        String sql = "select b.yq as \"yq\", b.jl as \"jl\",b.hz as \"hz\",b.cggsId as \"cggsid\",b.sbgs_id as \"sbgsId\", " +
                " b.cdId as \"cdid\",b.ck_id as \"ckid\",b.czggxh as \"czggxh\", b.kh as \"kh\",g.code as \"code\" , g.name as \"name\",g.ggxh as \"ggxh\"," +
                " g.dw as \"dw\", (select sj.z from t_sys_sjzdz sj where sj.id =b.cdId) as \"cd\"," +
                " (select ck.mc from t_sys_ck ck where ck.id = b.ck_id) as \"ck\" " +
                " from (select k.cd_sjzdz_id as cdId,k.jtwz_id,k.cd_sjzdz_id,k.ck_id,sum(k.sl) as jl ," +
                " decode(sum(k.sl * (shj.shj - shj.lxjt)),null,0,sum(k.sl * (shj.shj - shj.lxjt))) as hz," +
                " (select gg.cz||' '||gg.ggxh from t_sys_gg gg where gg.id = k.jtwz_id) as czggxh, " +
                " (select gs.jc from t_sys_gsjg gs where gs.id = k.sbgs_id) as kh,k.sbgs_id, " +
                " (select gss.jc from t_sys_gsjg gss where gss.id = k.cggs_id) as yq, k.cggs_id as cggsId" +
                " from t_biz_kh k  left join t_biz_shj shj on k.id = shj.kh_id" +
                " where k.qk = 0 group by k.cggs_id,k.sbgs_id,k.cd_sjzdz_id,k.ck_id,k.jtwz_id) b,t_sys_gg g where b.jtwz_id = g.id";

        if (StringUtils.isNotEmpty(kh)) {
            condition.add(" b.sbgs_id =:sbgs_id ");
            param.put("sbgs_id", kh);
        }

        if (YgService.getCurrentYgCxfw() != 0) {
            condition.add(" b.cggsId = :cggsId ");
            param.put("cggsId", YgService.getCurrentYgGsId());
        } else {
            if (null != cggsid) {
                condition.add(" b.cggsId = :cggsId ");
                param.put("cggsId", cggsid);
            }
        }

        if (StringUtils.isNotEmpty(code)) {
            condition.add(" g.code like :code ");
            param.put("code", "%" + code + "%");
        }

        if (StringUtils.isNotEmpty(name)) {
            condition.add(" g.name like :name ");
            param.put("name", "%" + name + "%");
        }

        if (StringUtils.isNotEmpty(ggxh)) {
            condition.add(" b.czggxh like :czggxh ");
            param.put("czggxh", "%" + ggxh + "%");
        }

        String whereSql = "";
        if (!condition.isEmpty())
            whereSql = " and " + StringUtils.join(condition, " and ");
        String orderSql = "";
        if (StringUtils.isNotBlank(sort) && StringUtils.isNotBlank(order)) {
            orderSql = " order by b." + sort + " " + order;
        } else {
            orderSql = " order by b.kh desc ";
        }
        String resultSql = sql + whereSql + orderSql;
        BigDecimal count = (BigDecimal) createSQLQuery(countsql + whereSql).setProperties(param).uniqueResult();
        page.setTotalCount(count.intValue());
        if (count.intValue() != 0) {
            List<Map> list = createSQLQuery(resultSql)
                    .setProperties(param)
                    .setFirstResult((pageNo - 1) * pageSize)
                    .setMaxResults(pageSize)
                    .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                    .list();
            page.setResult(list);
        } else {
            page.setResult(new ArrayList<Map>());
        }
        return page;
    }

    public Map khkctjHj(String kh, Long cggsid, String code, String name, String ggxh) {
        Map<String, Object> param = new HashMap<String, Object>();
        List<String> condition = new ArrayList<String>();
        String sumSql = "select decode(sum(jl),null,0,sum(jl)) as \"jl\",decode(sum(hz),null,0,sum(hz)) as \"hz\"  " +
                " from (select k.cggs_id as cggsId,k.cd_sjzdz_id as cdId,k.jtwz_id,k.cd_sjzdz_id,k.ck_id, " +
                " sum(k.sl) as jl ,sum(k.sl * (shj.shj-shj.lxjt)) as hz, (select gs.jc from t_sys_gsjg gs where gs.id = k.sbgs_id) as kh,k.sbgs_id, " +
                " (select gg.cz||' '||gg.ggxh from t_sys_gg gg where gg.id = k.jtwz_id) as czggxh " +
                " from t_biz_kh k left join t_biz_shj shj on k.id = shj.kh_id where k.qk = 0  " +
                " group by k.cggs_id,k.sbgs_id,k.cd_sjzdz_id,k.ck_id,k.jtwz_id) b,t_sys_gg g where b.jtwz_id = g.id ";

        if (StringUtils.isNotEmpty(kh)) {
            condition.add(" b.sbgs_id =:sbgs_id ");
            param.put("sbgs_id", kh);
        }

        if (YgService.getCurrentYgCxfw() != 0) {
            condition.add(" b.cggsId = :cggsId ");
            param.put("cggsId", YgService.getCurrentYgGsId());
        } else {
            if (null != cggsid) {
                condition.add(" b.cggsId = :cggsId ");
                param.put("cggsId", cggsid);
            }
        }

        if (StringUtils.isNotEmpty(code)) {
            condition.add(" g.code like :code ");
            param.put("code", "%" + code + "%");
        }

        if (StringUtils.isNotEmpty(name)) {
            condition.add(" g.name like :name ");
            param.put("name", "%" + name + "%");
        }

        if (StringUtils.isNotEmpty(ggxh)) {
            condition.add(" b.czggxh like :czggxh ");
            param.put("czggxh", "%" + ggxh + "%");
        }

        String whereSql = "";
        if (!condition.isEmpty())
            whereSql = " and " + StringUtils.join(condition, " and ");

        return (Map) createSQLQuery(sumSql + whereSql).setProperties(param).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).uniqueResult();
    }

    public List findKhkctjChart(Long khChart, String codeChart, String nameChart, String ggxhChart, Long cggsidChart, String tj) {

        String sql = "select kh,hz,cggs_id from (select (select g.jc from t_sys_gsjg g where g.id = b.sbgs_id) as kh, " +
                "  decode(sum(b.sl*(shj.shj-shj.lxjt)),null,0,sum(b.sl*(shj.shj-shj.lxjt))) as hz,b.cggs_id " +
                " from (select k.sbgs_id,k.cggs_id,gg.code,gg.name,k.sl,k.id," +
                " (select gg.cz||' '||gg.ggxh from t_sys_gg gg where gg.id = k.jtwz_id) as czggxh " +
                " from t_biz_kh k left join t_sys_gg gg on k.jtwz_id = gg.id where k.qk = 0 ";

        String orderSql = null;
        String whereSql = null;

        if (null != khChart) {
            sql += " and k.sbgs_id = " + khChart;
        }

        if (StringUtils.isNotBlank(codeChart)) {
            sql += " and gg.code like '%" + codeChart + "%'";
        }

        if (StringUtils.isNotBlank(nameChart)) {
            sql += " and gg.name like '%" + nameChart + "%'";
        }

        if (StringUtils.isNotBlank(ggxhChart)) {
            sql += " and gg.cz||' '||gg.ggxh like '%" + ggxhChart + "%'";
        }

        sql += " ) b left join t_biz_shj shj on b.id = shj.kh_id  group by b.sbgs_id,b.cggs_id )c ";

        if (StringUtils.equals("0", tj)) {
            whereSql = " where rownum <= 10";
            orderSql = " order by hz desc ";
        } else if (StringUtils.equals("-1", tj)) {
            whereSql = " where rownum <= 10";
            orderSql = " order by hz asc ";
        } else {
            whereSql = " where rownum <= " + Integer.parseInt(tj);
            orderSql = " order by hz desc ";
        }

        if (YgService.getCurrentYgCxfw() != 0) {
            if (cggsidChart != null) {
                whereSql += " and cggs_id = " + cggsidChart;
            } else {
                whereSql += " and cggs_id = " + YgService.getCurrentYgGsId();
            }
        }
        return this.findBySQL(sql + whereSql + orderSql);
    }

    public Page<Map> findKhkhztjList(String kh, Long cggsid, String code, String name, String ggxh, Date khkhztjrq, String sort, String order, int pageNo, int pageSize) {
        Page<Map> page = new Page<Map>();
        Map<String, Object> param = new HashMap<String, Object>();
        List<String> condition = new ArrayList<String>();
        String countsql = "select count(1) from ( ";

        String sql = "select yq as \"yq\",kh as \"kh\" ,code as \"code\",name as \"name\",czggxh as \"czggxh\"," +
                " dw as \"dw\",ck as \"ck\",cd as \"cd\" ,jl as \"jl\",khz as \"khz\"  from ( select " +
                " kh.cggs_id,(select gs.jc from t_sys_gsjg gs where gs.id = kh.cggs_id) as yq, " +
                " kh.sbgs_id,(select gss.jc from t_sys_gsjg gss where gss.id = kh.sbgs_id) as kh, " +
                " gg.code,gg.name,gg.dw,ck.mc as ck ,sj.z as cd , " +
                " (select g.cz||' '||g.ggxh from t_sys_gg g where g.id = kh.jtwz_id) as czggxh, " +
                " decode(sum(kh.kkdsl),null,0,sum(kh.kkdsl)) as jl,decode(sum(kh.kkdsl * mx.z),null,0,sum(kh.kkdsl * mx.z)) as khz " +
                "  from t_biz_kh kh  " +
                "  left join t_biz_syl_mx mx on kh.id = mx.kh_id and  ";
        if (null == khkhztjrq) {
            sql += " to_char(jsrq,'yyyyMMdd')  =  to_char(sysdate,'yyyyMMdd')";
        } else {
            sql += " to_char(jsrq,'yyyyMMdd') = " + sDaysNowDate(khkhztjrq);
        }
        String sql1 = "  left join t_sys_gg gg on kh.jtwz_id = gg.id " +
                "  left join t_sys_ck ck on kh.ck_id = ck.id " +
                "  left join t_sys_sjzdz sj on kh.cd_sjzdz_id = sj.id " +
                "  group by kh.cggs_id,kh.sbgs_id,kh.jtwz_id,ck.mc,gg.code,gg.dw,gg.name,sj.z) c  " +
                " where 1 = 1 ";

        if (StringUtils.isNotEmpty(kh)) {
            condition.add(" c.sbgs_id =:sbgs_id ");
            param.put("sbgs_id", kh);
        }

        if (YgService.getCurrentYgCxfw() != 0) {
            condition.add(" c.cggs_id =:cggs_id ");
            param.put("cggs_id", YgService.getCurrentYgGsId());
        } else {
            if (cggsid != null) {
                condition.add(" c.cggs_id = :cggs_id ");
                param.put("cggs_id", cggsid);
            }
        }

        if (StringUtils.isNotEmpty(code)) {
            condition.add(" c.code like :code ");
            param.put("code", "%" + code + "%");
        }

        if (StringUtils.isNotEmpty(name)) {
            condition.add(" c.name like :name ");
            param.put("name", "%" + name + "%");
        }

        if (StringUtils.isNotEmpty(ggxh)) {
            condition.add(" c.czggxh like :czggxh ");
            param.put("czggxh", "%" + ggxh + "%");
        }

        String whereSql = "";
        if (!condition.isEmpty())
            whereSql = " and " + StringUtils.join(condition, " and ");
        String orderSql = "";
        if (StringUtils.isNotBlank(sort) && StringUtils.isNotBlank(order)) {
            orderSql = " order by c." + sort + " " + order;
        } else {
            orderSql = " order by c.kh desc ";
        }
        String resultSql = sql + sql1 + whereSql + orderSql;
        BigDecimal count = (BigDecimal) createSQLQuery(countsql + sql + sql1 + whereSql + ")").setProperties(param).uniqueResult();
        page.setTotalCount(count.intValue());
        if (count.intValue() != 0) {
            List<Map> list = createSQLQuery(resultSql)
                    .setProperties(param)
                    .setFirstResult((pageNo - 1) * pageSize)
                    .setMaxResults(pageSize)
                    .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                    .list();
            page.setResult(list);
        } else {
            page.setResult(new ArrayList<Map>());
        }
        return page;
    }


    public String sDaysNowDate(Date date) {
        SimpleDateFormat formatter = new SimpleDateFormat("yyyyMMdd");
        return formatter.format(date);
    }


    public Map khkhztjHj(String kh, Long cggsid, String code, String name, String ggxh, Date khkhztjrq) {
        Map<String, Object> param = new HashMap<String, Object>();
        List<String> condition = new ArrayList<String>();

        String sumSql = "select sum(jl) as \"jlhj\",sum(khz) as \"khzhj\" from ( select " +
                " kh.cggs_id,(select gs.jc from t_sys_gsjg gs where gs.id = kh.cggs_id) as yq, " +
                " kh.sbgs_id,(select gss.jc from t_sys_gsjg gss where gss.id = kh.sbgs_id) as kh, " +
                " gg.code,gg.name,gg.dw,ck.mc as ck ,sj.z as cd , " +
                " (select g.cz||' '||g.ggxh from t_sys_gg g where g.id = kh.jtwz_id) as czggxh, " +
                " decode(sum(kh.kkdsl),null,0,sum(kh.kkdsl)) as jl,decode(sum(kh.kkdsl * mx.z),null,0,sum(kh.kkdsl * mx.z)) as khz " +
                "  from t_biz_kh kh  " +
                "  left join t_biz_syl_mx mx on kh.id = mx.kh_id and ";
        if (null == khkhztjrq) {
            sumSql += " to_char(jsrq,'yyyyMMdd')  =  to_char(sysdate,'yyyyMMdd')";
        } else {
            sumSql += " to_char(jsrq,'yyyyMMdd') = " + sDaysNowDate(khkhztjrq);
        }
        String sumSql1 = "  left join t_sys_gg gg on kh.jtwz_id = gg.id " +
                "  left join t_sys_ck ck on kh.ck_id = ck.id " +
                "  left join t_sys_sjzdz sj on kh.cd_sjzdz_id = sj.id " +
                "  group by kh.cggs_id,kh.sbgs_id,kh.jtwz_id,ck.mc,gg.code,gg.dw,gg.name,sj.z) c " +
                " where 1 = 1 ";
        if (StringUtils.isNotEmpty(kh)) {
            condition.add(" c.sbgs_id =:sbgs_id");
            param.put("sbgs_id", kh);
        }

        if (YgService.getCurrentYgCxfw() != 0) {
            condition.add(" c.cggs_id =:cggs_id ");
            param.put("cggs_id", YgService.getCurrentYgGsId());
        } else {
            if (cggsid != null) {
                condition.add(" c.cggs_id = :cggs_id ");
                param.put("cggs_id", cggsid);
            }
        }

        if (StringUtils.isNotEmpty(code)) {
            condition.add(" c.code like :code ");
            param.put("code", "%" + code + "%");
        }

        if (StringUtils.isNotEmpty(name)) {
            condition.add(" c.name like :name ");
            param.put("name", "%" + name + "%");
        }

        if (StringUtils.isNotEmpty(ggxh)) {
            condition.add(" c.czggxh like :czggxh ");
            param.put("czggxh", "%" + ggxh + "%");
        }

        String whereSql = "";
        if (!condition.isEmpty())
            whereSql = " and " + StringUtils.join(condition, " and ");
        return (Map) createSQLQuery(sumSql + sumSql1 + whereSql).setProperties(param).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).uniqueResult();
    }

    public List findKhkhztjChart(String codeChart, String nameChart, String ggxhChart, Long khId, Long cggsidChart, String data) {

        String sql = "select kh,khz,sbgs_id,cggs_id from (select  kh,sum(khz) as khz,sbgs_id,cggs_id from ( " +
                " select kh.cggs_id,(select gs.jc from t_sys_gsjg gs where gs.id = kh.cggs_id) as yq, " +
                " kh.sbgs_id,(select gss.jc from t_sys_gsjg gss where gss.id = kh.sbgs_id) as kh, " +
                " gg.code,gg.name,gg.dw,ck.mc as ck ,sj.z as cd , " +
                " (select g.cz||' '||g.ggxh from t_sys_gg g where g.id = kh.jtwz_id) as czggxh, " +
                " decode(sum(kh.kkdsl),null,0,sum(kh.kkdsl)) as jl,decode(sum(kh.kkdsl * mx.z),null,0,sum(kh.kkdsl * mx.z)) as khz " +
                "  from t_biz_kh kh  " +
                "  left join t_biz_syl_mx mx on kh.id = mx.kh_id and to_char(jsrq,'yyyyMMdd') = " + data +
                "  left join t_sys_gg gg on kh.jtwz_id = gg.id " +
                "  left join t_sys_ck ck on kh.ck_id = ck.id " +
                "  left join t_sys_sjzdz sj on kh.cd_sjzdz_id = sj.id " +
                "  where kh.deleted = 0 and kh.qk = 0 " +
                "  group by kh.cggs_id,kh.sbgs_id,kh.jtwz_id,ck.mc,gg.code,gg.dw,gg.name,sj.z) c where 1=1  ";
        String sql1 = " group by cggs_id,sbgs_id,kh,yq)  ";

        if (null != khId) {
            sql += " and sbgs_id = " + khId;
        }

        if (YgService.getCurrentYgCxfw() != 0) {
            if (cggsidChart != null) {
                sql += " and cggs_id = " + cggsidChart;
            } else {
                sql += " and cggs_id = " + YgService.getCurrentYgGsId();
            }
        }

        if (StringUtils.isNotBlank(codeChart)) {
            sql += " and code like '%" + codeChart + "%'";
        }

        if (StringUtils.isNotBlank(nameChart)) {
            sql += " and name like '%" + nameChart + "%'";
        }

        if (StringUtils.isNotBlank(ggxhChart)) {
            sql += " and czggxh like '%" + ggxhChart + "%'";
        }

        return this.findBySQL(sql + sql1);
    }


    public Page<Map> findSyltjList(String kh, Float syl, Date jsrqStart, Date jsrqEnd, int pageNo, int pageSize) {
        Page<Map> page = new Page<Map>();
        Map<String, Object> param = new HashMap<String, Object>();
        List<String> condition = new ArrayList<String>();

        String countsql = "select count(1) from t_biz_syl s left join t_sys_gsjg g on  s.khid = g.id left join t_sys_jzsyl jz on s.khid = jz.khid ";

        String sqlColumn = "select s.id as \"id\", s.khid as \"sbgsId\",g.qc as \"kh\",s.syl*100 as \"syl\", jz.jzsyl as \"jzsyl\"," +
                " (select gs.qc from t_sys_gsjg gs where gs.createorgan_id = gs.id ) as \"yq\",to_char(s.jsrq,'yyyy-MM-dd') as \"jsrq\"" +
                " from t_biz_syl s left join t_sys_gsjg g on  s.khid = g.id  left join t_sys_jzsyl jz on s.khid = jz.khid ";

        if (StringUtils.isNotEmpty(kh)) {
            condition.add(" s.khid =:khid ");
            param.put("khid", kh);
        }

        if (YgService.getCurrentYgCxfw() != 0) {
            condition.add(" s.createorgan_id = :organId ");
            param.put("organId", YgService.getCurrentYgGsId());
        }

        if (null != syl) {
            condition.add(" s.syl <= :syl ");
            param.put("syl", syl / 100);
        }

        if (null != jsrqStart || null != jsrqEnd) {
            if (jsrqStart != null) {
                condition.add(" s.jsrq >= :jsrqStart ");
                param.put("jsrqStart", jsrqStart);
            }

            if (!isEqual(jsrqStart, jsrqEnd)) {
                if (jsrqEnd != null) {
                    condition.add(" s.jsrq <= :jsrqEnd ");
                    param.put("jsrqEnd", jsrqEnd);
                }
            }

        } else {
            condition.add(" to_char(s.jsrq,'yyyyMMdd') = to_char(sysdate,'yyyyMMdd') ");
        }
        String whereSql = "";
        if (!condition.isEmpty())
            whereSql = " where " + StringUtils.join(condition, " and ");
        String orderSql = " order by s.jsrq";
        String resultSql = sqlColumn + whereSql + orderSql;
        BigDecimal count = (BigDecimal) createSQLQuery(countsql + whereSql).setProperties(param).uniqueResult();
        page.setTotalCount(count.intValue());
        if (count.intValue() != 0) {
            List<Map> list = createSQLQuery(resultSql)
                    .setProperties(param)
                    .setFirstResult((pageNo - 1) * pageSize)
                    .setMaxResults(pageSize)
                    .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                    .list();
            page.setResult(list);
        } else {
            page.setResult(new ArrayList<Map>());
        }
        return page;
    }

    private Boolean isEqual(Date startDate, Date endDate) {
        SimpleDateFormat formatter = new SimpleDateFormat("yyyyMMdd");
        if (formatter.format(startDate).equals(formatter.format(endDate))) {
            return true;
        }
        return false;
    }


    public Qyjg findSyltjKh(Long khChart) {
        if (null != khChart) {
            return this.qyjgDao.get(khChart);
        }
        return null;
    }

    public List findKhSyl(Long sbgsid, String date) {
        String sql = "select s.syl*100 as syl from t_biz_syl s where s.khid = ? and " +
                " to_char(s.jsrq,'yyyy-MM-dd') = ? order by s.jsrq desc";
        return this.findBySQL(sql, sbgsid, date);
    }


    public List findKhYjz(Long cggsidChart) {
        String sql = " select gs.qc ,yjz.yjz from t_biz_yjz yjz,t_sys_gsjg gs " +
                " where yjz.createorgan_id = gs.id ";
        if (null != cggsidChart) {
            sql += "and yjz.createorgan_id = " + cggsidChart;
        }
        return this.findBySQL(sql);
    }

    public Page<Map> findSyltjList(String qcName, String jcName, int pageNo, int pageSize) {
        Page<Map> page = new Page<Map>();
        Map<String, Object> param = new HashMap<String, Object>();
        List<String> condition = new ArrayList<String>();

        String countsql = " select count(1) from t_sys_gsjg g left join  t_sys_jzsyl jz on g.id =jz.khid  " +
                " where g.type = 'qyjg' and (g.lskh = 0 or g.lskh is null) and g.deleted = 0 and (jz.deleted = 0 or jz.deleted is null) ";

        String sqlColumn = "select g.id as \"id\",g.qc as \"qc\",g.jc as \"jc\",g.bm as \"bm\",jz.jzsyl as \"jzsyl\"," +
                " jz.last_updated as \"updatedDate\",jz.date_created as \"createdDate\" " +
                " from t_sys_gsjg g left join  t_sys_jzsyl jz on g.id =jz.khid " +
                " where g.type = 'qyjg' and (g.lskh = 0 or g.lskh is null) and g.deleted = 0  and (jz.deleted = 0 or jz.deleted is null)";

        if (StringUtils.isNotEmpty(qcName)) {
            condition.add(" g.qc like :qcName ");
            param.put("qcName", "%" + qcName.trim() + "%");
        }

        if (StringUtils.isNotEmpty(jcName)) {
            condition.add(" g.jc like :jcName ");
            param.put("jcName", "%" + jcName.trim() + "%");
        }

        String whereSql = "";
        if (!condition.isEmpty())
            whereSql = " and " + StringUtils.join(condition, " and ");
        String orderSql = " order by g.date_created  desc";
        String resultSql = sqlColumn + whereSql + orderSql;
        BigDecimal count = (BigDecimal) createSQLQuery(countsql + whereSql).setProperties(param).uniqueResult();
        page.setTotalCount(count.intValue());
        if (count.intValue() != 0) {
            List<Map> list = createSQLQuery(resultSql)
                    .setProperties(param)
                    .setFirstResult((pageNo - 1) * pageSize)
                    .setMaxResults(pageSize)
                    .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                    .list();
            page.setResult(list);
        } else {
            page.setResult(new ArrayList<Map>());
        }
        return page;
    }

    public List<Map> findExportQyjgSyl() {
        String sql = "select g.qc as qc ,g.jc as jc ,jz.jzsyl as jzsyl " +
                " from t_sys_gsjg g left join  t_sys_jzsyl jz on g.id =jz.khid " +
                " where g.type = 'qyjg' and (g.lskh = 0 or g.lskh is null) and g.deleted = 0 " +
                " and (jz.deleted = 0 or jz.deleted is null) and jz.jzsyl is null ";
        return (List<Map>) createSQLQuery(sql).setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE).list();
    }

    public List<Map> findQyjgSylByKhId(Long khId) {
        String sql = "select jz.jzsyl as \"jzsyl\"  from t_sys_jzsyl jz where jz.khid = ? ";
        return (List<Map>) createSQLQuery(sql, khId).setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE).list();
    }
}
